import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.Properties;

import c4.common.Constants;


public class CustomerLoginActivity extends Activity {

	public CustomerLoginActivity(Properties props) {
		super(props);
	}
	
	public ActivityResult execute(Resource db, Resource noss) {
		CorporateDatabase database = null;
		ActivityResult result= new ActivityResult();
		
		
		if(!(db instanceof CorporateDatabase)) {
			result.setResult(-1);
		}
		else {
			database = (CorporateDatabase)db;
			String query = "";
			ResultSet rs, locked;

			//See if customer exists
			query = "";
				
			query += "SELECT * FROM account_information " +
					 "WHERE account_number=" + (String)parameters.getProperty(Constants.ACCOUNT_NUMBER) + 
					 " AND pin='" + (String)parameters.getProperty(Constants.PIN) + "';";
			
			rs = database.executeQuery(query);
			
			try {
			
				if (rs.next()) { //customer exists
					
					//ensure that account is not locked
					query = "";
					
					query += 	"SELECT * " +
								"FROM account_locks " +
								"WHERE account_number=" + (String)parameters.getProperty(Constants.ACCOUNT_NUMBER) + ";";
					
					locked = database.executeQuery(query);
					
					if (locked.first()) { //account is locked
						result.setResult(-1);
						result.addErrorMessage((String) locked.getString(Constants.DESCRIPTION));
					}
					else {
						result.setResult(1);
						
						query = "";
						
						query += "SELECT * FROM account_information " +
								 "WHERE account_number=" + (String)parameters.getProperty(Constants.ACCOUNT_NUMBER) + 
								 " AND pin='" + (String)parameters.getProperty(Constants.PIN) + "';";
						
						rs = database.executeQuery(query);
						
						rs.next();
						
						//iterate through and get all of the value to set in the properties of ActivityResult
						result.addProperty(Constants.ACCOUNT_NUMBER, rs.getString(Constants.ACCOUNT_NUMBER));
						result.addProperty(Constants.NAME, rs.getString(Constants.NAME));
						result.addProperty(Constants.DOB,rs.getString(Constants.DOB));
						result.addProperty(Constants.SSN, rs.getString(Constants.SSN));
						result.addProperty(Constants.STREET,rs.getString(Constants.STREET));
						result.addProperty(Constants.ZIP,rs.getString(Constants.ZIP));
						result.addProperty(Constants.CITY,rs.getString(Constants.CITY));
						result.addProperty(Constants.STATE,rs.getString(Constants.STATE));
						result.addProperty(Constants.VOICEMAIL,rs.getString(Constants.VOICEMAIL));
						result.addProperty(Constants.CALLER_ID, rs.getString(Constants.CALLER_ID));
						result.addProperty(Constants.THREE_WAY,rs.getString(Constants.THREE_WAY));
						result.addProperty(Constants.DISTANCE_PROVIDER,rs.getString(Constants.DISTANCE_PROVIDER));
						result.addProperty(Constants.PIN, (String)parameters.getProperty(Constants.PIN));
						String account_number = rs.getString(Constants.ACCOUNT_NUMBER);
						
						//get phone numbers
						
						query = "";
						
						query = "SELECT phone_number FROM phone_number_listing WHERE account_number=" +
								account_number;
						
						rs = database.executeQuery(query);
						
						int index = 0;
						
						while(rs.next()){
							result.addProperty("phone" + index, rs.getString(Constants.PHONE_NUMBER) );
							index++;
						}
						
						//get service requests
						query = "";
						
						query = "SELECT service_number, description, service_status " +
								"FROM service_request_log where account_number=" +
								account_number;
						
						rs = database.executeQuery(query);
						
						index = 0;
						
						while(rs.next()){
							result.addProperty("service" + index, rs.getString(Constants.SERVICE_NUMBER) + ", " + 
									rs.getString(Constants.DESCRIPTION) + "," + rs.getString(Constants.SERVICE_STATUS));
							index++;
						}
						
						//get trouble tickets
						query = "";
						
						query = "SELECT ticket_number, description, ticket_status " +
								"FROM trouble_ticket_log where account_number=" +
								account_number;
						
						rs = database.executeQuery(query);
						
						index = 0;
						
						while(rs.next()){
							result.addProperty("trouble" + index, rs.getString(Constants.TICKET_NUMBER) + ", " + 
									rs.getString(Constants.DESCRIPTION) + "," + rs.getString(Constants.TICKET_STATUS));
							index++;
						}
					}
				}
				else { //customer doesn't exist
					result.setResult(-1);
					result.addErrorMessage("Customer not found in database. Please revalidate account number and pin \n");
				}
			} 
			catch (SQLException e) {
					e.printStackTrace();
					result.setResult(-1);
			}
		}
		return result;
	}

	 
	public String getType() {
		return Constants.DATABASE;
	}
}
